package cn.com.checne.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import cn.com.checne.model.domain.IntroduceCategory;
import cn.com.checne.model.domain.IntroduceInfo;
import cn.com.checne.model.domain.LanguageInfo;
import cn.com.checne.model.dto.IntroduceQuery;
import cn.com.checne.util.TimeUtils;


@Repository
public class IntroduceInfoDao {
	protected static final Logger log = Logger.getLogger(IntroduceInfoDao.class);

	/**
	 * create
	 * @param cate
	 * @return
	 */
	public boolean create(IntroduceInfo Introduce) {
		boolean result = false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String createSql = "insert into tbl_introduce(lg_id_fk,introduce_cate_id_fk,introduce_title,introduce_summary,introduce_publishdate,introduce_publishfrom,introduce_author,introduce_contenttype,introduce_content," +
					"introduce_meta_keyword,introduce_meta_content,introduce_isshowindex,introduce_isshowlist,introduce_istop,introduce_firstimage)" +
					" values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			PreparedStatement pstmt = conn.prepareStatement(createSql);
			int i = 0 ;
			pstmt.setInt(++i,Introduce.getLanguageId());
			pstmt.setInt(++i,Introduce.getCateId());
			pstmt.setString(++i,Introduce.getTitle());
			pstmt.setString(++i,Introduce.getSummary());
			pstmt.setTimestamp(++i, new java.sql.Timestamp(Introduce.getPublishDate().getTime()));
			pstmt.setString(++i,Introduce.getPublishFrom());
			pstmt.setString(++i,Introduce.getAuthor());
			pstmt.setInt(++i,Introduce.getContentType());
			pstmt.setString(++i,Introduce.getContent());
			
			pstmt.setString(++i,Introduce.getMetaKeyword());
			pstmt.setString(++i,Introduce.getMetaContent());
			
			pstmt.setInt(++i,Introduce.getIsShowIndex());
			pstmt.setInt(++i,Introduce.getIsShowList());
			pstmt.setInt(++i,Introduce.getIsTop());
			
			pstmt.setString(++i,Introduce.getFirstImage());

			pstmt.execute();
			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false ;
		}
		return result;
	}

	/**
	 * @param cate
	 * @return
	 */
	public boolean update(IntroduceInfo Introduce) {
		boolean result = false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String updateSql = "update tbl_introduce set " +
					"lg_id_fk = ?,introduce_cate_id_fk = ?,introduce_title = ?,introduce_summary = ?,introduce_publishdate =? ,introduce_publishfrom = ?,introduce_author =?,introduce_contenttype = ? ,introduce_content=?," +
					"introduce_meta_keyword =?,introduce_meta_content=?,introduce_isshowindex=?,introduce_isshowlist=?,introduce_istop=?,introduce_firstimage=? where introduce_id = ? ";
			PreparedStatement pstmt = conn.prepareStatement(updateSql);
			int i = 0 ;
			pstmt.setInt(++i,Introduce.getLanguageId());
			pstmt.setInt(++i,Introduce.getCateId());
			pstmt.setString(++i,Introduce.getTitle());
			pstmt.setString(++i,Introduce.getSummary());
			pstmt.setTimestamp(++i, new java.sql.Timestamp(Introduce.getPublishDate().getTime()));
			pstmt.setString(++i,Introduce.getPublishFrom());
			pstmt.setString(++i,Introduce.getAuthor());
			pstmt.setInt(++i,Introduce.getContentType());
			pstmt.setString(++i,Introduce.getContent());
			
			pstmt.setString(++i,Introduce.getMetaKeyword());
			pstmt.setString(++i,Introduce.getMetaContent());
			
			pstmt.setInt(++i,Introduce.getIsShowIndex());
			pstmt.setInt(++i,Introduce.getIsShowList());
			pstmt.setInt(++i,Introduce.getIsTop());
			
			pstmt.setString(++i,Introduce.getFirstImage());
			
			pstmt.setInt(++i, Introduce.getIntroduceId());
			
			pstmt.executeUpdate();
			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false ;
		}
		return result;
	}

	/**
	 * @param id
	 * @return
	 */
	public boolean delete(int id){
		boolean result = false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String updateSql = "delete from tbl_introduce where introduce_id = ? ";
			PreparedStatement pstmt = conn.prepareStatement(updateSql);
			int i = 0 ;
			pstmt.setInt(++i,id);
			log.info("[SQL]" + pstmt.toString());
			pstmt.executeUpdate();
			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false ;
		}
		return result ;
	}
	/**
	 * 
	 * @param IntroduceId
	 * @return
	 */
	public IntroduceInfo getIntroduceById(int IntroduceId) {
		IntroduceInfo Introduce = new IntroduceInfo();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_introduce where introduce_id = ? ";

			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, IntroduceId);
			log.info("[SQL]" + pstmt.toString());
			ResultSet rs = pstmt.executeQuery();
			List<IntroduceInfo> list = getResult(rs);
			Introduce = (list != null && list.size() > 0 ) ? list.get(0) : Introduce ;
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Introduce;
	}
	/**
	 * 
	 * @param query
	 * @param start
	 * @param num
	 * @return
	 */
	public List<IntroduceInfo> getIntroduceListByCondition(IntroduceQuery query,int start,int num) {
		List<IntroduceInfo> list = new ArrayList<IntroduceInfo>();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_introduce where 1=1 " ;
			if(query.getLanguageId() > 0){
				sql += " and lg_id_fk = ? " ;
			}
			if(query.getCateId() > 0){
				sql += " and introduce_cate_id_fk = ? " ;
			}
			if(query.getIsShowIndex() > -1){
				sql += " and introduce_isshowindex = ? " ;
			}
			if(query.getIsShowList() > -1){
				sql += " and introduce_isshowlist = ? " ;
			}
			if(query.getIsTop() > -1){
				sql += " and introduce_istop = ? " ;
			}
			if(StringUtils.isNotBlank(query.getKw())){
				sql += " and (introduce_title like ? or introduce_meta_keyword like ? or introduce_meta_content like ? or introduce_content like ? )";
			}
			if(StringUtils.isNotBlank(query.getStartTime())){
				sql += " and introduce_publishdate >= ? ";
			}
			if(StringUtils.isNotBlank(query.getEndTime())){
				sql += " and introduce_publishdate <= ? ";
			}
			sql += "order by introduce_publishdate desc limit ?,?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			int i = 0 ;
			if(query.getLanguageId() > 0){
				pstmt.setInt(++i, query.getLanguageId());
			}
			if(query.getCateId() > 0){
				pstmt.setInt(++i, query.getCateId());
			}
			if(query.getIsShowIndex() > -1){
				pstmt.setInt(++i, query.getIsShowIndex());
			}
			if(query.getIsShowList() > -1){
				pstmt.setInt(++i, query.getIsShowList());
			}
			if(query.getIsTop() > -1){
				pstmt.setInt(++i, query.getIsTop());
			}
			if(StringUtils.isNotBlank(query.getKw())){
				pstmt.setString(++i, "%" + query.getKw() + "%");
				pstmt.setString(++i, "%" + query.getKw() + "%");
				pstmt.setString(++i, "%" + query.getKw() + "%");
				pstmt.setString(++i, "%" + query.getKw() + "%");
			}
			if(StringUtils.isNotBlank(query.getStartTime())){
				pstmt.setTimestamp(++i, new java.sql.Timestamp(TimeUtils.formatDate(query.getStartTime()).getTime()));
			}
			if(StringUtils.isNotBlank(query.getEndTime())){
				pstmt.setTimestamp(++i, new java.sql.Timestamp(TimeUtils.formatDate(query.getEndTime()).getTime()));
			}
			pstmt.setInt(++i, start);
			pstmt.setInt(++i, num);
			log.info("[SQL]" + pstmt.toString());
			ResultSet rs = pstmt.executeQuery();
			list.addAll(getResult(rs));
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 
	 * @param query
	 * @return
	 */
	public int getIntroduceCountByCondition(IntroduceQuery query) {
		int total = 0 ;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select count(1) as total from tbl_introduce where 1=1 " ;
			if(query.getLanguageId() > 0){
				sql += " and lg_id_fk = ? " ;
			}
			if(query.getCateId() > 0){
				sql += " and introduce_cate_id_fk = ? " ;
			}
			if(query.getIsShowIndex() > -1){
				sql += " and introduce_isshowindex = ? " ;
			}
			if(query.getIsShowList() > -1){
				sql += " and introduce_isshowlist = ? " ;
			}
			if(query.getIsTop() > -1){
				sql += " and introduce_istop = ? " ;
			}
			if(StringUtils.isNotBlank(query.getKw())){
				sql += " and (introduce_title like ? or introduce_meta_keyword like ? or introduce_meta_content like ? or introduce_content like ? )";
			}
			if(StringUtils.isNotBlank(query.getStartTime())){
				sql += " and introduce_publishdate >= ? ";
			}
			if(StringUtils.isNotBlank(query.getEndTime())){
				sql += " and introduce_publishdate <= ? ";
			}
			PreparedStatement pstmt = conn.prepareStatement(sql);
			int i = 0 ;
			if(query.getLanguageId() > 0){
				pstmt.setInt(++i, query.getLanguageId());
			}
			if(query.getCateId() > 0){
				pstmt.setInt(++i, query.getCateId());
			}
			if(query.getIsShowIndex() > -1){
				pstmt.setInt(++i, query.getIsShowIndex());
			}
			if(query.getIsShowList() > -1){
				pstmt.setInt(++i, query.getIsShowList());
			}
			if(query.getIsTop() > -1){
				pstmt.setInt(++i, query.getIsTop());
			}
			if(StringUtils.isNotBlank(query.getKw())){
				pstmt.setString(++i, "%" + query.getKw() + "%");
				pstmt.setString(++i, "%" + query.getKw() + "%");
				pstmt.setString(++i, "%" + query.getKw() + "%");
				pstmt.setString(++i, "%" + query.getKw() + "%");
			}
			if(StringUtils.isNotBlank(query.getStartTime())){
				pstmt.setTimestamp(++i, new java.sql.Timestamp(TimeUtils.formatDate(query.getStartTime()).getTime()));
			}
			if(StringUtils.isNotBlank(query.getEndTime())){
				pstmt.setTimestamp(++i, new java.sql.Timestamp(TimeUtils.formatDate(query.getEndTime()).getTime()));
			}
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				total = rs.getInt("total");
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return total;
	}
	
	
	public boolean setShowIndex(int flag,int...IntroduceIds){
		boolean result = false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String updateSql = "update tbl_introduce set introduce_isshowindex = ? where introduce_id = ? ";
			PreparedStatement pstmt = conn.prepareStatement(updateSql);
			for(int IntroduceId : IntroduceIds){
				int i = 0 ;
				pstmt.setInt(++i,flag);
				pstmt.setInt(++i,IntroduceId);
				pstmt.addBatch();
			}
			pstmt.executeBatch();
			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false ;
		}
		return result ;
	}
	
	public boolean setShowList(int flag,int...IntroduceIds){
		boolean result = false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String updateSql = "update tbl_introduce set introduce_isshowlist = ? where introduce_id = ? ";
			PreparedStatement pstmt = conn.prepareStatement(updateSql);
			for(int IntroduceId : IntroduceIds){
				int i = 0 ;
				pstmt.setInt(++i,flag);
				pstmt.setInt(++i,IntroduceId);
				pstmt.addBatch();
			}
			pstmt.executeBatch();
			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false ;
		}
		return result ;
	}
	
	public IntroduceInfo isIntroduceExist(int languageId ,int cateId){
		IntroduceInfo Introduce = null ;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_introduce where lg_id_fk = ? and introduce_cate_id_fk = ?   " ;
			PreparedStatement pstmt = conn.prepareStatement(sql);
			int i = 0 ;
			pstmt.setInt(++i, languageId);
			pstmt.setInt(++i, cateId);
			log.info("[SQL]" + pstmt.toString());
			ResultSet rs = pstmt.executeQuery();
			List<IntroduceInfo> list = getResult(rs);
			Introduce = (list != null && list.size() > 0 ) ? list.get(0) : null;
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Introduce ;
	}
	/**
	 * get rseult from resultSet
	 * @param rs
	 * @return
	 */
	public List<IntroduceInfo> getResult(ResultSet rs) {
		List<IntroduceInfo> list = new ArrayList<IntroduceInfo>();
		try {
			IntroduceInfo Introduce = null ;
			while(rs.next()) {
				Introduce = new IntroduceInfo();
				Introduce.setIntroduceId(rs.getInt("introduce_id"));
				Introduce.setTitle(rs.getString("introduce_title"));
				Introduce.setSummary(rs.getString("introduce_summary"));
				Introduce.setPublishDate(rs.getTimestamp("introduce_publishdate"));
				Introduce.setPublishFrom(rs.getString("introduce_publishfrom"));
				Introduce.setAuthor(rs.getString("introduce_author"));
				Introduce.setContentType(rs.getInt("introduce_contenttype"));
				Introduce.setContent(rs.getString("introduce_content"));
				Introduce.setMetaKeyword(rs.getString("introduce_meta_keyword"));
				Introduce.setMetaContent(rs.getString("introduce_meta_content"));
				Introduce.setIsShowIndex(rs.getInt("introduce_isshowindex"));
				Introduce.setIsShowList(rs.getInt("introduce_isshowlist"));
				Introduce.setIsTop(rs.getInt("introduce_istop"));
				Introduce.setFirstImage(rs.getString("introduce_firstimage"));
				
				Introduce.setLanguageId(rs.getInt("lg_id_fk"));
				LanguageInfo lg = new LanguageInfoDao().getLanguageInfoById(Introduce.getLanguageId());
				Introduce.setLanguageInfo(lg);
				
				Introduce.setCateId(rs.getInt("introduce_cate_id_fk"));
				IntroduceCategory cate = new IntroduceCategoryDao().getIntroduceCategory(Introduce.getCateId());
				Introduce.setIntroduceCategory(cate);
				
				list.add(Introduce);
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
}